<?php

function get_customer_testimonial() {
  $query = db_select('node', 'n')->extend('PagerDefault');
  $query->innerJoin('field_data_field_description', 'td', 'n.vid = td.revision_id');
  $query->innerJoin('field_data_field_image_testimonial', 'ti', 'n.vid = ti.revision_id');
  $query->leftJoin('file_managed', 'fm', 'fm.fid = ti.field_image_testimonial_fid');
  $query->fields('n', array('nid', 'type', 'title'));
  $query->addField('td', 'field_description_value', 'description');
  $query->addField('n', 'title', 'name_testimonials');
  $query->addField('fm', 'uri', 'uri_image');
  $query->condition('n.status', 1);
  $query->condition('n.type', 'testimonials');
  $query->limit(3);
  return $query->execute();
}

function get_customer_infomation($uid) {
  $query = db_select('tvn_customer', 'c');
  $query->innerJoin('users', 'u', 'c.uid = u.uid');
  $query->innerJoin('file_managed', 'f', 'u.picture = f.fid');
  $query->fields('c');
  $query->addField('f', 'uri');
  $query->condition('c.uid', $uid);
  return $query->execute()->fetchObject();
}

function get_pre_recorded_video($random = false) {
  $query = db_select('node', 'n');
  $query->leftJoin('field_data_field_gender', 'fg', 'n.uid=fg.revision_id');
  $query->innerJoin('field_data_field_video_pre_video_recorded', 'pvr', 'n.nid=pvr.revision_id');
  $query->innerJoin('field_data_field_video_cost', 'fvc', 'n.nid=fvc.revision_id');
  $query->innerJoin('file_managed', 'fm', 'pvr.field_video_pre_video_recorded_fid=fm.fid');
  $query->innerJoin('url_alias', 'ua', "ua.source = CONCAT('node/', n.nid)");
  $query->fields('n', array('nid', 'type', 'title', 'uid'));
  $query->addField('fvc', 'field_video_cost_value', 'cost');
  $query->addField('fm', 'uri');
  $query->addField('ua', 'alias');
  $query->addField('fg', 'field_gender_value', 'gender');
  $query->condition('n.status', 1);
  $query->condition('n.type', 'video');
  if ($random) {
    $query->addExpression('Rand()', 'n.nid');
  }
  $query->condition(db_or()->condition('fm.filemime', 'image/png', '=')->condition('fm.filemime', 'image/jpeg', '=')->condition('fm.filemime', 'image/jpg', '='));
  return $query->execute();
}

function tvn_user_exists($uid) {
  $result = db_query("SELECT COUNT(*) FROM {users} WHERE uid = :uid", array(':uid' => $uid))->fetchField();
  return $result;
}

function get_search_trainer_online($male, $female, $genre, $cost) {
  $arr_cost = explode('-', $cost);
  $query = db_select('users', 'u');
  $query->innerJoin('sessions', 's', 'u.uid=s.uid');
  $query->innerJoin('users_roles', 'ur', 'u.uid=ur.uid');
  $query->innerJoin('role', 'r', 'ur.rid=r.rid');
  $query->leftJoin('field_data_field_customer_name', 'fcn', 'u.uid=fcn.revision_id');
  $query->leftJoin('field_data_field_customer_surname', 'fcs', 'u.uid=fcs.revision_id');
  $query->leftJoin('field_data_field_gender', 'fg', 'u.uid=fg.revision_id');
  $query->fields('u', array('uid', 'name', 'mail'));
  $query->addField('fcn', 'field_customer_name_value', 'fcn_name');
  $query->addField('fcs', 'field_customer_surname_value', 'surname');
  $query->addField('fg', 'field_gender_value', 'gender');
  $query->condition('r.name', 'trainer');
  //    $query->condition('tc.genre', $genre);
  if ($male != -1 && $female != -1) {
    $query->condition(db_or()->condition('fg.field_gender_value', $male)->condition('fg.field_gender_value', $female));
  } elseif ($male != -1 && $female == -1) {
    $query->condition('fg.field_gender_value', $male);
  } elseif ($male == -1 && $female != -1) {
    $query->condition('fg.field_gender_value', $female);
  }
  //    switch($arr_cost[0]) {
  //        case '0':
  //            $query->condition('tc.credit', 0);
  //            break;
  //        case '16':
  //            $query->condition('tc.credit', 16, '>=');
  //            break;
  //        case 'false':
  //            break;
  //        default :
  //            $query->condition('tc.credit', (int)$arr_cost[0], '>=');
  //            $query->condition('tc.credit', (int)$arr_cost[1], '<=');
  //            break;
  //    }
  $query->groupBy('s.uid');
  return $query->execute();
}

function get_search_pre_recorded($male, $female, $genre, $cost) {
  $arr_cost = explode('-', $cost);
  $query = db_select('node', 'n');
  $query->leftJoin('field_data_field_gender', 'fg', 'n.uid=fg.revision_id');
  $query->innerJoin('field_data_field_video_pre_video_recorded', 'pvr', 'n.nid=pvr.revision_id');
  $query->innerJoin('field_data_field_video_cost', 'fvc', 'n.nid=fvc.revision_id');
  $query->innerJoin('file_managed', 'fm', 'pvr.field_video_pre_video_recorded_fid=fm.fid');
  $query->innerJoin('url_alias', 'ua', "ua.source = CONCAT('node/', n.nid)");
  $query->fields('n', array('nid', 'type', 'title', 'uid'));
  $query->addField('fvc', 'field_video_cost_value', 'cost');
  $query->addField('fm', 'uri');
  $query->addField('ua', 'alias');
  $query->addField('fg', 'field_gender_value', 'gender');
  $query->condition('n.status', 1);
  $query->condition('n.type', 'video');
  $query->condition(db_or()->condition('fm.filemime', 'image/png', '=')->condition('fm.filemime', 'image/jpeg', '=')->condition('fm.filemime', 'image/jpg', '='));
  if ($male != -1 && $female != -1) {
    $query->condition(db_or()->condition('fg.field_gender_value', $male)->condition('fg.field_gender_value', $female));
  } elseif ($male != -1 && $female == -1) {
    $query->condition('fg.field_gender_value', $male);
  } elseif ($male == -1 && $female != -1) {
    $query->condition('fg.field_gender_value', $female);
  }
  switch ($arr_cost[0]) {
    case '0':
      $query->condition('fvc.field_video_cost_value', 0);
      break;
    case '16':
      $query->condition('fvc.field_video_cost_value', 16, '>=');
      break;
    case 'false':
      break;
    default :
      $query->condition('fvc.field_video_cost_value', (int) $arr_cost[0], '>=');
      $query->condition('fvc.field_video_cost_value', (int) $arr_cost[1], '<=');
      break;
  }
  return $query->execute();
}

function get_news_feed() {
  $query = db_select('node', 'n')->extend('PagerDefault');
  $query->innerJoin('field_data_field_description', 'fd', 'n.vid=fd.revision_id');
  $query->leftJoin('field_data_body', 'b', 'n.vid=b.revision_id');
  $query->innerJoin('field_data_field_type_news', 'tn', 'n.vid=tn.revision_id');
  $query->leftJoin('field_data_field_feed_link', 'fl', 'n.vid=fl.revision_id');
  $query->fields('n', array('nid', 'type', 'title', 'created'));
  $query->fields('tn', array('field_type_news_value'));
  $query->addField('fd', 'field_description_value', 'description');
  $query->addField('b', 'body_value', 'body');
  $query->condition('n.type', 'news');
  $query->condition('n.status', 1);
  $query->condition('tn.field_type_news_value', 'Feed');
  $query->orderBy('n.nid', 'DESC');
  $query->limit(2);
  return $query->execute();
}

function get_customer_following_trainer($uid) {
  $query = db_select('users', 'u')->extend('PagerDefault');
  $query->innerJoin('field_revision_field_customer_name','cn','cn.revision_id = u.uid');
  $query->innerJoin('tvn_follow', 'tf', 'u.uid = tf.follow_id');
//  $query->innerJoin('tvn_customer', 'c', 'c.uid = u.uid');
  $query->leftJoin('file_managed', 'f', 'u.picture = f.fid');
//  $query->fields('c', array('name', 'surname'));
  $query->addField('cn','field_customer_name_value','name');
  $query->addField('cn','revision_id','uid');
  $query->addField('f', 'uri');
  $query->orderBy('u.uid', 'DESC');
  $query->condition('tf.user_id', $uid);
  $query->condition('u.status', 1);
  $query->limit(4);
  return $query->execute();
}

function get_my_image($uid) {
  $query = db_select('file_managed', 'fm')->extend('PagerDefault');
  $query->innerJoin('field_data_field_customer_image', 'ci', 'fm.fid = ci.field_customer_image_fid');
  $query->fields('fm', array('filename', 'uri'));
  $query->condition('fm.uid', $uid);
  $query->limit(2);
  return $query->execute();
}

function get_my_video($uid) {
  $query = db_select('node', 'n')->extend('PagerDefault');
  $query->innerJoin('field_data_field_video_duration', 'vd', 'n.vid = vd.revision_id');
  $query->innerJoin('field_data_field_video_cost', 'vc', 'n.vid = vc.revision_id');
  $query->innerJoin('field_data_field_video', 'fv', 'n.vid = fv.revision_id');
  $query->innerJoin('field_data_field_video_pre_video_recorded', 'vr', 'n.vid  = vr.revision_id');
  $query->innerJoin('file_managed', 'fm', 'fm.fid = fv.field_video_fid');
  $query->fields('fm', array('filename', 'uri'));
  $query->fields('n', array('nid', 'title'));
  $query->condition('fm.uid', $uid);
  $query->condition('n.type', 'video');
  $query->orderBy('n.created', 'DESC');
  $query->limit(2);
  return $query->execute();
}

function tvn_user_update_picture($fid) {
  global $user;
  $query = db_update('users');
  $query->fields(array('picture' => $fid));
  $query->condition('uid', $user->uid);
  return $query->execute();
}

function tvn_get_all_trainer() {
  $query = db_select('users', 'u');
  $query->innerJoin('users_roles', 'ur', 'u.uid = ur.uid');
  $query->innerJoin('role', 'r', 'r.rid = ur.rid');
  $query->fields('u', array('uid', 'name'));
  $query->condition('u.status', 1);
  $query->condition('r.name', 'trainer');
  $rs = $query->execute();
  $list[0] = 'none';
  while ($row = $rs->fetchObject()) {
    $list[$row->uid] = $row->name;
  }
  return $list;
}

function tvn_update_news_about_trainer($nid, $uid) {
  $query = db_insert('tvn_news_about_trainer')->fields(array('nid' => $nid, 'uid' => $uid));
  return $query->execute();
}

function tvn_customer_following_other_db($follow_id) {
  global $user;
  $query = db_insert('tvn_follow')->fields(array('user_id'=> $user->uid,'follow_id' => $follow_id));
  return $query->execute();
}